﻿using ClosedXML.Excel;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace WaterResources
{
    public partial class FormWaterEnvironment : Form
    {
        public FormWaterEnvironment()
        {
            InitializeComponent();

            var db = new WaterEnvironmentDb<WaterEnvironment>();
            var wsSource = db.Find();
            foreach (WaterEnvironment we in wsSource) 
            {
                if (we.Level==1) { we.Levelname = "第一级"; }
                else if (we.Level == 2) { we.Levelname = "第二级"; }
                else if (we.Level == 3) { we.Levelname = "第三级"; }
                else if (we.Level == 4) { we.Levelname = "第四级"; }
                else if (we.Level == 5) { we.Levelname = "第五级"; }
            }
            dataGridView1.DataSource = wsSource;
            mapDataGridViewHeader();
        }

        private void FormWaterSecurity_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.OpenForms[0].Show();
        }

        private string validateInputIsInt(string input)
        {
            var output = input;
            var regex = new Regex(@"^\d*$");
            if (!regex.IsMatch(input))
            {
                regex = new Regex(@"[^\d]");
                output = regex.Replace(input, "");
            }
            return output;
        }

        private string validateInputIsDecimal(string input)
        {
            var output = input;
            var regex = new Regex(@"^\d+\.?\d*$");
            if (!regex.IsMatch(input))
            {
                regex = new Regex(@"[^\d\.]");
                output = regex.Replace(input, "");
            }
            while (output.IndexOf('.') != output.LastIndexOf('.'))
            {
                output = output.Substring(0, output.LastIndexOf('.'));
            }
            return output;
        }

        private void mapDataGridViewHeader()
        {
            dataGridView1.Columns["Id"].HeaderText = "编号";
            dataGridView1.Columns["Id"].Width=55;
            dataGridView1.Columns["Level"].HeaderText = "河道等级";
            dataGridView1.Columns["Level"].Width = 80;
            dataGridView1.Columns["Level"].Visible = false;
            dataGridView1.Columns["Levelname"].HeaderText = "等级";
            dataGridView1.Columns["Name"].HeaderText = "河道名称";
            dataGridView1.Columns["Name"].Width = 80;
            dataGridView1.Columns["Area"].HeaderText = "河道所属辖区";
            dataGridView1.Columns["Area"].Width = 110;
            dataGridView1.Columns["Start"].HeaderText = "河道起点";
            dataGridView1.Columns["Start"].Width = 80;
            dataGridView1.Columns["End"].HeaderText = "河道终点";
            dataGridView1.Columns["End"].Width = 80;
            dataGridView1.Columns["targetL1"].HeaderText = "取样河道水深(m)";
            dataGridView1.Columns["targetL1"].Width = 80;
            dataGridView1.Columns["targetL2"].HeaderText = "试验水柱高度(m)";
            dataGridView1.Columns["targetL2"].Width = 80;
            dataGridView1.Columns["targetC1"].HeaderText = "目标水质(mg/L)";
            dataGridView1.Columns["targetC1"].Width = 80;
            dataGridView1.Columns["targetC2"].HeaderText = "实验上覆水水质(mg/L)";
            dataGridView1.Columns["targetC2"].Width = 100;
            dataGridView1.Columns["targetC3"].HeaderText = "平衡后河道水质(mg/L)";
            dataGridView1.Columns["targetC3"].Width = 100;
            dataGridView1.Columns["WaterNA"].HeaderText = "目标水质总氮(mg/L)";
            dataGridView1.Columns["WaterNA"].Width = 100;
            dataGridView1.Columns["WaterNA"].Visible = false;
            dataGridView1.Columns["WaterPA"].HeaderText = "目标水质总磷(mg/L)";
            dataGridView1.Columns["WaterPA"].Width = 100;
            dataGridView1.Columns["WaterPA"].Visible = false;
            dataGridView1.Columns["WaterCODA"].HeaderText = "目标水质COD(mg/L)";
            dataGridView1.Columns["WaterCODA"].Width = 100;
            dataGridView1.Columns["WaterCODA"].Visible = false;
            dataGridView1.Columns["WaterNB"].HeaderText = "试验上浮水总氮(mg/L)";
            dataGridView1.Columns["WaterNB"].Width = 100;
            dataGridView1.Columns["WaterPB"].HeaderText = "试验上浮水总磷(mg/L)";
            dataGridView1.Columns["WaterPB"].Width = 100;
            dataGridView1.Columns["WaterCODB"].HeaderText = "试验上浮水COD(mg/L)";
            dataGridView1.Columns["WaterCODB"].Width = 100;
            dataGridView1.Columns["WaterNC"].HeaderText = "总氮释放风险";
            dataGridView1.Columns["WaterNC"].Width = 110;
            dataGridView1.Columns["WaterPC"].HeaderText = "总磷释放风险";
            dataGridView1.Columns["WaterPC"].Width = 110;
            dataGridView1.Columns["WaterCODC"].HeaderText = "COD释放风险";
            dataGridView1.Columns["Time"].HeaderText = "录入时间";
        }

        private void level_Scroll(object sender, EventArgs e)
        {
            var level = sender as TrackBar;
            switch (level.Value)
            {
                case 1: levelText.Text = "第一级"; break;
                case 2: levelText.Text = "第二级"; break;
                case 3: levelText.Text = "第三级"; break;
                case 4: levelText.Text = "第四级"; break;
                case 5: levelText.Text = "第五级"; break;
                default: levelText.Text = "第一级"; break;
            }
        }

        //private void riverWidth_TextChanged(object sender, EventArgs e)
        //{
        //    var textBoxRiverWidth = sender as TextBox;
        //    riverWidth.Text = validateInputIsDecimal(textBoxRiverWidth.Text);
        //}

        //private void ratio_TextChanged(object sender, EventArgs e)
        //{
        //    var textBoxRatio = sender as TextBox;
        //    ratio.Text = validateInputIsDecimal(textBoxRatio.Text);
        //}

        private void elevation_TextChanged(object sender, EventArgs e)
        {
            var textBoxElevation = sender as TextBox;
            WaterNB.Text = validateInputIsDecimal(textBoxElevation.Text);
        }

        private void line_TextChanged(object sender, EventArgs e)
        {
            var textBoxLine = sender as TextBox;
            WaterPB.Text = validateInputIsDecimal(textBoxLine.Text);
        }

        private void safe_TextChanged(object sender, EventArgs e)
        {
            var textBoxSafe = sender as TextBox;
            WaterCODB.Text = validateInputIsDecimal(textBoxSafe.Text);
        }


        private void buttonSubmit_Click(object sender, EventArgs e)
        {
            string nc = "";
            string pc = "";
            string codc = "";
            if (name.Text == "" || area.Text == "" || start.Text == "" || end.Text == "" || WaterNB.Text == "" || WaterPB.Text == "" || WaterCODB.Text == "" || targetL2.Text == "" || targetC1.Text == "" || targetC2.Text == "")
            {
                MessageBox.Show("警告:请可输入的框中值不为空！");
                return;
            }
            else if (!area.Text.Contains("市") && !area.Text.Contains("县") && !area.Text.Contains("区") && !area.Text.Contains("乡") && !area.Text.Contains("一般"))
            {
                MessageBox.Show("河道所属辖区，请填写关键字【市】或者【县】或者【区】或者【乡】或者【一般】！");
                return;
            }
            if (targetL1.Text == "" || Convert.ToDecimal(targetL1.Text) == 0)
            {
                MessageBox.Show("警告:请填写正确的【取样河道水深L1】！");
                return;
            }
            else if (WaterNA.Text == "")
            {
                MessageBox.Show("警告:请选择目标水质类别！");
                return;
            }
            WRFormula wrf = new WRFormula();
            if (Convert.ToDouble(WaterNA.Text)==0.2) {
                if (Convert.ToDouble(WaterNB.Text) > 0.2)
                {
                    nc = "存在释放风险";
                }
                else {
                    nc = "不存在释放风险";
                }
                if (Convert.ToDouble(WaterPB.Text) > 0.02)
                {
                    pc = "存在释放风险";
                }
                else {
                    pc = "不存在释放风险";
                }
                if (Convert.ToDouble(WaterCODB.Text) > 15)
                {
                    codc = "存在释放风险";
                }
                else
                {
                    codc = "不存在释放风险";
                }
            }
            else if (Convert.ToDouble(WaterNA.Text) == 0.5)
            {
                if (Convert.ToDouble(WaterNB.Text) > 0.5)
                {
                    nc = "存在释放风险";
                }
                else
                {
                    nc = "不存在释放风险";
                }
                if (Convert.ToDouble(WaterPB.Text) > 0.01)
                {
                    pc = "存在释放风险";
                }
                else
                {
                    pc = "不存在释放风险";
                }
                if (Convert.ToDouble(WaterCODB.Text) > 15)
                {
                    codc = "存在释放风险";
                }
                else
                {
                    codc = "不存在释放风险";
                }
            }
            else if (Convert.ToDouble(WaterNA.Text) == 1)
            {
                if (Convert.ToDouble(WaterNB.Text) > 1)
                {
                    nc = "存在释放风险";
                }
                else
                {
                    nc = "不存在释放风险";
                }
                if (Convert.ToDouble(WaterPB.Text) > 0.2)
                {
                    pc = "存在释放风险";
                }
                else
                {
                    pc = "不存在释放风险";
                }
                if (Convert.ToDouble(WaterCODB.Text) > 20)
                {
                    codc = "存在释放风险";
                }
                else
                {
                    codc = "不存在释放风险";
                }
            }
            else if (Convert.ToDouble(WaterNA.Text) == 1.5)
            {
                if (Convert.ToDouble(WaterNB.Text) > 1.5)
                {
                    nc = "存在释放风险";
                }
                else
                {
                    nc = "不存在释放风险";
                }
                if (Convert.ToDouble(WaterPB.Text) > 0.3)
                {
                    pc = "存在释放风险";
                }
                else
                {
                    pc = "不存在释放风险";
                }
                if (Convert.ToDouble(WaterCODB.Text) > 30)
                {
                    codc = "存在释放风险";
                }
                else
                {
                    codc = "不存在释放风险";
                }
            }
            else if (Convert.ToDouble(WaterNA.Text) == 2)
            {
                if (Convert.ToDouble(WaterNB.Text) > 2)
                {
                    nc = "存在释放风险";
                }
                else
                {
                    nc = "不存在释放风险";
                }
                if (Convert.ToDouble(WaterPB.Text) > 0.4)
                {
                    pc = "存在释放风险";
                }
                else
                {
                    pc = "不存在释放风险";
                }
                if (Convert.ToDouble(WaterCODB.Text) > 40)
                {
                    codc = "存在释放风险";
                }
                else
                {
                    codc = "不存在释放风险";
                }
            }
            
            var ws = new WaterEnvironment
            {
                Level = level.Value,
                Name = name.Text,
                Area = area.Text,
                Start = start.Text,
                End = end.Text,
                WaterNA = Convert.ToDecimal(Math.Round(Convert.ToDouble(WaterNA.Text),3)),
                WaterPA = Convert.ToDecimal(Math.Round(Convert.ToDouble(WaterPA.Text), 3)),
                WaterCODA = Convert.ToDecimal(Math.Round(Convert.ToDouble(WaterCODA.Text), 3)),
                WaterNB = Convert.ToDecimal(Math.Round(Convert.ToDouble(WaterNB.Text), 3)),
                WaterPB = Convert.ToDecimal(Math.Round(Convert.ToDouble(WaterPB.Text), 3)),
                WaterCODB = Convert.ToDecimal(Math.Round(Convert.ToDouble(WaterCODB.Text), 3)),
                WaterNC = nc,
                WaterPC = pc,
                WaterCODC = codc,
                targetL1= Convert.ToDecimal(Math.Round(Convert.ToDouble(targetL1.Text), 3)),
                targetL2 = Convert.ToDecimal(Math.Round(Convert.ToDouble(targetL2.Text), 3)),
                targetC1 = Convert.ToDecimal(Math.Round(Convert.ToDouble(targetC1.Text), 3)),
                targetC2 = Convert.ToDecimal(Math.Round(Convert.ToDouble(targetC2.Text), 3)),
                targetC3 = Convert.ToDecimal(wrf.EnvironmentFormula(Convert.ToDouble(targetC1.Text), Convert.ToDouble(targetC2.Text), Convert.ToDouble(targetL1.Text), Convert.ToDouble(targetL2.Text))),
                Time = DateTime.Now,

            };
            if (!string.IsNullOrEmpty(id.Text) && new Regex(@"\d").IsMatch(id.Text))
            {
                ws.Id = Convert.ToInt32(id.Text);
            }
            
            var db = new WaterEnvironmentDb<WaterEnvironment>();
            int num;
            if (ws.Id == 0)
            {
                num = db.Save(ws);
            }
            else
            {
                num = db.Edit(ws);
            }
            if (num == 1)
            {
                targetC3.Text = ws.targetC3.ToString();
                WaterNA.Text = ws.WaterNA.ToString();
                WaterPA.Text = ws.WaterPA.ToString();
                if (ws.WaterPC.ToString() == "存在释放风险")
                {
                    WaterPC.ForeColor = System.Drawing.Color.Red;
                }
                else {
                    WaterPC.ForeColor = System.Drawing.Color.Green;
                }
                if (ws.WaterNC.ToString() == "存在释放风险")
                {
                    WaterNC.ForeColor = System.Drawing.Color.Red;
                }
                else
                {
                    WaterNC.ForeColor = System.Drawing.Color.Green;
                }
                if (ws.WaterCODC.ToString() == "存在释放风险")
                {
                    WaterCODC.ForeColor = System.Drawing.Color.Red;
                }
                else
                {
                    WaterCODC.ForeColor = System.Drawing.Color.Green;
                }
                WaterCODA.Text = ws.WaterCODA.ToString();
                WaterNC.Text = ws.WaterNC;
                WaterPC.Text = ws.WaterPC;
                WaterCODC.Text = ws.WaterCODC;
                MessageBox.Show(ws.Id == 0 ? "添加成功" : "修改成功");
                var wsSource = db.Find();
                foreach (WaterEnvironment we in wsSource)
                {
                    if (we.Level == 1) { we.Levelname = "第一级"; }
                    else if (we.Level == 2) { we.Levelname = "第二级"; }
                    else if (we.Level == 3) { we.Levelname = "第三级"; }
                    else if (we.Level == 4) { we.Levelname = "第四级"; }
                    else if (we.Level == 5) { we.Levelname = "第五级"; }
                }
                dataGridView1.DataSource = wsSource;
                mapDataGridViewHeader();
            }
            else
            {
                MessageBox.Show("添加失败");
            }
        }

        private void buttonReset_Click(object sender, EventArgs e)
        {
            id.ResetText();
            level.Value = 1;
            levelText.Text = "第一级";
            name.ResetText();
            area.ResetText();
            start.ResetText();
            end.ResetText();
            targetL1.ResetText();
            targetL2.ResetText();
            targetC1.ResetText();
            targetC2.ResetText();
            targetC3.ResetText();
            WaterNA.ResetText();
            WaterPA.ResetText();
            WaterCODA.ResetText();
            WaterNB.ResetText();
            WaterPB.ResetText();
            WaterCODB.ResetText();
            WaterNC.ResetText();
            WaterPC.ResetText();
            WaterCODC.ResetText();
        }

        private void buttonQuit_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void dataGridView_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            var dgv = sender as DataGridView;
            var cells = dgv.SelectedRows[0].Cells;
            id.Text = cells["Id"].Value.ToString();
            level.Value = Convert.ToInt32(cells["Level"].Value);
            switch (level.Value)
            {
                case 1: levelText.Text = "第一级"; break;
                case 2: levelText.Text = "第二级"; break;
                case 3: levelText.Text = "第三级"; break;
                case 4: levelText.Text = "第四级"; break;
                case 5: levelText.Text = "第五级"; break;
                default: levelText.Text = "第一级"; break;
            }
            name.Text = cells["Name"].Value.ToString();
            area.Text = cells["Area"].Value.ToString();
            start.Text = cells["Start"].Value.ToString();
            end.Text = cells["End"].Value.ToString();
            targetL1.Text = cells["targetL1"].Value.ToString();
            targetL2.Text = cells["targetL2"].Value.ToString();
            targetC1.Text = cells["targetC1"].Value.ToString();
            targetC2.Text = cells["targetC2"].Value.ToString();
            targetC3.Text = cells["targetC3"].Value.ToString();
            WaterNA.Text = cells["WaterNA"].Value.ToString();
            WaterPA.Text = cells["WaterPA"].Value.ToString();
            WaterCODA.Text = cells["WaterCODA"].Value.ToString();
            WaterNB.Text = cells["WaterNB"].Value.ToString();
            WaterPB.Text = cells["WaterPB"].Value.ToString();
            WaterCODB.Text = cells["WaterCODB"].Value.ToString();
            WaterNC.Text = cells["WaterNC"].Value.ToString();
            WaterPC.Text = cells["WaterPC"].Value.ToString();
            WaterCODC.Text = cells["WaterCODC"].Value.ToString();

            if (cells["WaterCODA"].Value.ToString() == "40")
            {
                button5.Focus();
            }
            else if (cells["WaterCODA"].Value.ToString() == "30") 
            {
                button4.Focus();
            }
            else if (cells["WaterCODA"].Value.ToString() == "20")
            {
                button3.Focus();
            }
            else if (cells["WaterCODA"].Value.ToString() == "15"&& cells["WaterNA"].Value.ToString()=="0.5")
            {
                button2.Focus();
            }
            else if (cells["WaterCODA"].Value.ToString() == "15" && cells["WaterNA"].Value.ToString() == "0.2")
            {
                button1.Focus();
            }

            if (cells["WaterPC"].Value.ToString() == "存在释放风险")
            {
                WaterPC.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                WaterPC.ForeColor = System.Drawing.Color.Green;
            }
            if (cells["WaterNC"].Value.ToString() == "存在释放风险")
            {
                WaterNC.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                WaterNC.ForeColor = System.Drawing.Color.Green;
            }
            if (cells["WaterCODC"].Value.ToString() == "存在释放风险")
            {
                WaterCODC.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                WaterCODC.ForeColor = System.Drawing.Color.Green;
            }
        }

        private void dataGridView_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                dataGridView1.ClearSelection();
                dataGridView1.Rows[e.RowIndex].Selected = true;
                dataGridViewMenu.Show(MousePosition.X, MousePosition.Y);
            }
        }

        private void menuDelete_Click(object sender, EventArgs e)
        {
            var id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells["Id"].Value);
            var db = new WaterEnvironmentDb<WaterEnvironment>();
            int num;
            if (id > 0)
            {
                num = db.Drop(id);
                if (num == 1)
                {
                    MessageBox.Show("删除成功");
                    var wsSource = db.Find();
                    dataGridView1.DataSource = wsSource;
                    //mapDataGridViewHeader();
                }
                else
                {
                    MessageBox.Show("删除失败");
                }
            }
            else
            {
                MessageBox.Show("此列ID未找到");
            }
        }

        private void importMenu_Click(object sender, EventArgs e)
        {
            openFileDialog.Filter = "Excel文件|*.xlsx;*.xlsm";
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    using (var xlw = new XLWorkbook(openFileDialog.FileName))
                    {
                        var db = new WaterEnvironmentDb<WaterEnvironment>();
                        var rows = xlw.Worksheet(1).RowsUsed();
                        var firstCellRange = new List<string> { "河道等级", "1", "2", "3", "4", "5", "第一级", "第二级", "第三级", "第四级", "第五级" };
                        int i = 0;
                        foreach (var row in rows)
                        {
                            var cells = row.CellsUsed().AsList();
                            if (firstCellRange.IndexOf(cells[0].Value.ToString()) < 0 || cells.Count < 20)
                            {
                                MessageBox.Show("文件内容不符合模板，请确认后重新上传。");
                                return;
                            }
                            if (cells[0].Value.ToString() == "河道等级")
                            {
                                // 标题栏，跳过
                                continue;
                            }
                            if (!cells[2].Value.ToString().Contains("市") && !cells[2].Value.ToString().Contains("县") && !cells[2].Value.ToString().Contains("区") && !cells[2].Value.ToString().Contains("乡") && !cells[2].Value.ToString().Contains("一般"))
                            {
                                MessageBox.Show("第" + i + "行数据的河道所属辖区，没有关键字【市】或者【县】或者【区】或者【乡】或者【一般】！请先维护！");
                                return;
                            }
                            if (cells[1].Value.ToString() != "")
                            {
                                var dba = new WaterEnvironmentDb<WaterEnvironment>();
                                string sql = "select * from water_environment where Area='" + cells[2].Value.ToString() + "' and Name='" + cells[1].Value.ToString() + "'";
                                var w = dba.FindFirst(sql, new WaterEnvironment());
                                if (w != null)
                                {
                                    int level1;
                                    switch (cells[0].Value.ToString())
                                    {
                                        case "第一级": level1 = 1; break;
                                        case "第二级": level1 = 2; break;
                                        case "第三级": level1 = 3; break;
                                        case "第四级": level1 = 4; break;
                                        case "第五级": level1 = 5; break;
                                        default: level1 = 1; break;
                                    }
                                    w.Level = level1;
                                    w.Name = cells[1].Value.ToString();
                                    w.Area = cells[2].Value.ToString();
                                    w.Start = cells[3].Value.ToString();
                                    w.End = cells[4].Value.ToString();
                                    w.targetL1 = Convert.ToDecimal(cells[5].Value);
                                    w.targetL2 = Convert.ToDecimal(cells[6].Value);
                                    w.targetC1 = Convert.ToDecimal(cells[7].Value);
                                    w.targetC2 = Convert.ToDecimal(cells[8].Value);
                                    w.targetC3 = Convert.ToDecimal(cells[9].Value);
                                    w.WaterNA = Convert.ToDecimal(cells[10].Value);
                                    w.WaterPA = Convert.ToDecimal(cells[11].Value);
                                    w.WaterCODA = Convert.ToDecimal(cells[12].Value);
                                    w.WaterNB = Convert.ToDecimal(cells[13].Value);
                                    w.WaterPB = Convert.ToDecimal(cells[14].Value);
                                    w.WaterCODB = Convert.ToDecimal(cells[15].Value);
                                    w.WaterNC = cells[16].Value.ToString();
                                    w.WaterPC = cells[17].Value.ToString();
                                    w.WaterCODC = cells[18].Value.ToString();
                                    if (cells[19].Value.ToString() == "") {
                                        w.Time = DateTime.Now;
                                    }
                                    else {
                                        w.Time = Convert.ToDateTime(cells[19].Value.ToString());
                                    }
                                    dba.Edit(w);
                                    continue;
                                }
                            }
                            int level;
                            switch (cells[0].Value.ToString())
                            {
                                case "第一级": level = 1; break;
                                case "第二级": level = 2; break;
                                case "第三级": level = 3; break;
                                case "第四级": level = 4; break;
                                case "第五级": level = 5; break;
                                default: level = 1; break;
                            }
                            var t = DateTime.Now;
                            if (cells[19].Value.ToString() == "")
                            {
                                t = DateTime.Now;
                            }
                            else
                            {
                                t = Convert.ToDateTime(cells[19].Value.ToString());
                            }
                            var model = new WaterEnvironment
                            {
                                Level = level,
                                Name = cells[1].Value.ToString(),
                                Area = cells[2].Value.ToString(),
                                Start = cells[3].Value.ToString(),
                                End = cells[4].Value.ToString(),
                                targetL1 = Convert.ToDecimal(cells[5].Value),
                                targetL2 = Convert.ToDecimal(cells[6].Value),
                                targetC1 = Convert.ToDecimal(cells[7].Value),
                                targetC2 = Convert.ToDecimal(cells[8].Value),
                                targetC3 = Convert.ToDecimal(cells[9].Value),
                                WaterNA = Convert.ToDecimal(cells[10].Value),
                                WaterPA = Convert.ToDecimal(cells[11].Value),
                                WaterCODA = Convert.ToDecimal(cells[12].Value),
                                WaterNB = Convert.ToDecimal(cells[13].Value),
                                WaterPB = Convert.ToDecimal(cells[14].Value),
                                WaterCODB = Convert.ToDecimal(cells[15].Value),
                                WaterNC = cells[16].Value.ToString(),
                                WaterPC = cells[17].Value.ToString(),
                                WaterCODC = cells[18].Value.ToString(),
                                Time = t,
                            };
                            db.Save(model);
                        }
                        MessageBox.Show("导入成功");
                        var wsSource = db.Find();
                        foreach (WaterEnvironment we in wsSource)
                        {
                            if (we.Level == 1) { we.Levelname = "第一级"; }
                            else if (we.Level == 2) { we.Levelname = "第二级"; }
                            else if (we.Level == 3) { we.Levelname = "第三级"; }
                            else if (we.Level == 4) { we.Levelname = "第四级"; }
                            else if (we.Level == 5) { we.Levelname = "第五级"; }
                        }
                        dataGridView1.DataSource = wsSource;
                        mapDataGridViewHeader();
                    }
                }
                catch (IOException)
                {
                    MessageBox.Show("文件读取错误，文件进程被占用，请关闭后重试。");
                }
                catch (FileFormatException)
                {
                    MessageBox.Show("文件格式错误，程序只支持Excel 2007及以上版本，请重新保存为xlsx格式后重试。");
                }
                catch (Exception exp)
                {
                    MessageBox.Show("超出预期的错误:" + exp.Message);
                }
            }
        }

        private void exportMenu_Click(object sender, EventArgs e)
        {
            var saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel文件|*.xlsx";
            saveFileDialog.FileName = "水环境数据" + DateTime.Now.ToString("D") + ".xlsx";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    var dt = new DataTable();

                    foreach (DataGridViewColumn column in dataGridView1.Columns)
                    {
                        dt.Columns.Add(column.HeaderText, column.ValueType);
                    }

                    foreach (DataGridViewRow row in dataGridView1.Rows)
                    {
                        dt.Rows.Add();
                        foreach (DataGridViewCell cell in row.Cells)
                        {
                            dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
                        }
                    }

                    using (var xlw = new XLWorkbook())
                    {
                        xlw.Worksheets.Add(dt, "Sheet1");
                        xlw.SaveAs(saveFileDialog.FileName);
                    }
                }
                catch (IOException)
                {
                    MessageBox.Show("文件写入错误，文件进程被占用，请关闭后重试。");
                }
                catch (Exception exp)
                {
                    MessageBox.Show("超出预期的错误:" + exp.Message);
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            WaterNA.Text = "0.2";
            WaterPA.Text = "0.02";
            WaterCODA.Text = "15";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            WaterNA.Text = "0.5";
            WaterPA.Text = "0.1";
            WaterCODA.Text = "15";
        }

        private void button3_Click(object sender, EventArgs e)
        {
            WaterNA.Text = "1";
            WaterPA.Text = "0.2";
            WaterCODA.Text = "20";
        }

        private void button4_Click(object sender, EventArgs e)
        {
            WaterNA.Text = "1.5";
            WaterPA.Text = "0.3";
            WaterCODA.Text = "30";
        }

        private void button5_Click(object sender, EventArgs e)
        {
            WaterNA.Text = "2";
            WaterPA.Text = "0.4";
            WaterCODA.Text = "40";
        }

        private void targetL1_TextChanged(object sender, EventArgs e)
        {
            var textBoxRatio = sender as TextBox;
            targetL1.Text = validateInputIsDecimal(textBoxRatio.Text);
        }

        private void targetL2_TextChanged(object sender, EventArgs e)
        {
            var textBoxRatio = sender as TextBox;
            targetL2.Text = validateInputIsDecimal(textBoxRatio.Text);
        }

        private void targetC2_TextChanged(object sender, EventArgs e)
        {
            var textBoxRatio = sender as TextBox;
            targetC2.Text = validateInputIsDecimal(textBoxRatio.Text);
        }

        private void targetC1_TextChanged(object sender, EventArgs e)
        {
            var textBoxRatio = sender as TextBox;
            targetC1.Text = validateInputIsDecimal(textBoxRatio.Text);
        }
        
        private void FormWaterEnvironment_SizeChanged(object sender, EventArgs e)
        {
            panel1.Height = Height - 40;

        }

        private void FormWaterEnvironment_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.OpenForms[0].Show();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            string sql = "select * from water_environment where 1=1";
            if (checknam.Text!="") {
                sql += " and name like'%"+ checknam.Text + "%'";
            }
            if (checkarea.Text!="") {
                sql += " and area like'%"+checkarea.Text+"%'";
            }
            if (checklevel.Text == "第一级") {
                sql += " and level=1";
            } else if (checklevel.Text == "第二级") {
                sql += " and level=2";
            }
            else if (checklevel.Text == "第三级")
            {
                sql += " and level=3";
            }
            else if (checklevel.Text == "第四级")
            {
                sql += " and level=4";
            }
            else if (checklevel.Text == "第五级")
            {
                sql += " and level=5";
            }
            if (checktimeone.Value!=null && checktimetwo.Value!=null&& checktimeone.Value<= checktimetwo.Value) {
                sql += " and time between '"+ checktimeone.Value.ToString("yyyy-MM-dd") + "' and '" + checktimetwo.Value.ToString("yyyy-MM-dd") + "'";
            }
            var db = new WaterEnvironmentDb<WaterEnvironment>();
            var wsSource = db.Find(sql,new WaterEnvironment());
            foreach (WaterEnvironment we in wsSource)
            {
                if (we.Level == 1) { we.Levelname = "第一级"; }
                else if (we.Level == 2) { we.Levelname = "第二级"; }
                else if (we.Level == 3) { we.Levelname = "第三级"; }
                else if (we.Level == 4) { we.Levelname = "第四级"; }
                else if (we.Level == 5) { we.Levelname = "第五级"; }
            }
            dataGridView1.DataSource = wsSource;
            mapDataGridViewHeader();
        }
    }
}
